﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

namespace 毕业设计__排课软件
{
    /// <summary>
    /// Delete_Class.xaml 的交互逻辑
    /// </summary>
    public partial class Delete_Class : Window
    {
        public string Grade { get; set; }
        public string Major { get; set; }
        public string Class { get; set; }
        public string Time { get; set; }
        public List<string> CL = new List<string>();
        public List<string> TimeL = new List<string>();
        public List<string> DL = new List<string>();
        public List<string> TeacherL = new List<string>();
        readonly string _dbPath;
        public Delete_Class(string Grade, string Major, string Class, string Time)
        {
            InitializeComponent();
            CL.Add("全部");
            var path0 = Environment.CurrentDirectory;
            var path1 = path0.Substring(0, path0.LastIndexOf("\\", StringComparison.Ordinal));
            var path2 = path1.Substring(0, path1.LastIndexOf("\\", StringComparison.Ordinal));
            _dbPath = path2 + "\\DB\\bs_pk.db";
            using (var conn1 = new SQLiteConnection())
            {
                var connsb = new SQLiteConnectionStringBuilder { DataSource = _dbPath };
                conn1.ConnectionString = connsb.ToString();
                conn1.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(conn1))
                {
                    string sql1 = string.Format("select Course_Name,Classroom_Name,Teacher_Name,Teaching_Time from Configuration_Info, Lesson_Info, Classroom_Info, Teacher_Info, Class_Info where Configuration_Info.Course_ID = Lesson_Info.Course_ID and Configuration_Info.Teacher_ID = Teacher_Info.Teacher_ID and Configuration_Info.Classroom_ID = Classroom_Info.Classroom_ID and Configuration_Info.Class_ID = Class_Info.Class_ID and major = '{0}' and Class = '{1}' and Grade = '{2}' and Teaching_Time like '{3}' order BY Teaching_Time ASC,Course_Name ASC", Major, Class, Grade, Time);
                    cmd.CommandText = sql1;
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                    DataSet data = new DataSet();
                    adapter.Fill(data);
                    DataTable table = data.Tables[0];
                    foreach (DataRow tr in table.Rows)
                    {
                        string pattern = @"\d\d";
                        Match match = Regex.Match(tr["Teaching_Time"].ToString(), pattern);
                        if (CL.Contains(tr["Course_Name"].ToString()) == true)
                        {
                            TimeL.Add(match.Value + "周");
                            DL.Add(tr["Classroom_Name"].ToString());
                            TeacherL.Add(tr["Teacher_Name"].ToString());
                        }
                        else
                        {
                            CL.Add(tr["Course_Name"].ToString());
                            TimeL.Add(match.Value + "周");
                            DL.Add(tr["Classroom_Name"].ToString());
                            TeacherL.Add(tr["Teacher_Name"].ToString());
                        }
                    }
                }
            }
            Class_CB.ItemsSource = CL;
            Class_CB.SelectedIndex = 0;
        }

        private void bt2_Click(object sender, RoutedEventArgs e)
        {
            Close();
        }

        private void bt1_Click(object sender, RoutedEventArgs e)
        {
            if (Class_CB.SelectedItem.ToString() == "全部")
            {
                using (var conn = new SQLiteConnection())
                {
                    var connsb = new SQLiteConnectionStringBuilder { DataSource = _dbPath };
                    conn.ConnectionString = connsb.ToString();
                    conn.Open();
                    using (new SQLiteCommand(conn))
                    {
                        string sql = "DELETE from Configuration_Info WHERE Class_ID = (SELECT Class_ID FROM Class_Info where Grade = '" + Grade + "' and major = '" + Major + "' and Class = '" + Class + "' ) and Teaching_Time like '" + Time + "'";
                        var command = new SQLiteCommand(sql, conn);
                        var reader = command.ExecuteReader();
                    }
                }
            }
            else
            {
                using (var conn = new SQLiteConnection())
                {
                    string pattern = @"\d\d";
                    Match match = Regex.Match(Time, pattern);
                    Match match1 = Regex.Match(Time_CB.SelectedItem.ToString(), pattern);
                    var connsb = new SQLiteConnectionStringBuilder { DataSource = _dbPath };
                    conn.ConnectionString = connsb.ToString();
                    conn.Open();
                    using (new SQLiteCommand(conn))
                    {
                        string sql = string.Format("DELETE from Configuration_Info WHERE Configuration_ID = (select Configuration_ID from Configuration_Info, Lesson_Info, Classroom_Info, Teacher_Info, Class_Info where Configuration_Info.Course_ID = Lesson_Info.Course_ID and Configuration_Info.Teacher_ID = Teacher_Info.Teacher_ID and Configuration_Info.Classroom_ID = Classroom_Info.Classroom_ID and Configuration_Info.Class_ID = Class_Info.Class_ID AND major = '{0}' and Class = '{1}' and Grade = '{2}' and Course_Name = '{3}' and Teaching_Time = '{4}')", Major, Class, Grade, Class_CB.SelectedItem.ToString(), match1.Value+match.Value);
                        var command = new SQLiteCommand(sql, conn);
                        var reader = command.ExecuteReader();
                    }
                }
            }
            MessageBox.Show("删除成功！");
            Close();
        }

        private void Class_CB_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (Class_CB.SelectedItem.ToString() == "全部")
            {
                DiDian_CB.ItemsSource = null;
                Teacher_CB.ItemsSource = null;
                Time_CB.ItemsSource = null;
                return;
            }
            TimeL.Clear();
            DL.Clear();
            TeacherL.Clear();
            using (var conn1 = new SQLiteConnection())
            {
                var connsb = new SQLiteConnectionStringBuilder { DataSource = _dbPath };
                conn1.ConnectionString = connsb.ToString();
                conn1.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(conn1))
                {
                    string sql1 = string.Format("select Course_Name,Classroom_Name,Teacher_Name,Teaching_Time from Configuration_Info, Lesson_Info, Classroom_Info, Teacher_Info, Class_Info where Configuration_Info.Course_ID = Lesson_Info.Course_ID and Configuration_Info.Teacher_ID = Teacher_Info.Teacher_ID and Configuration_Info.Classroom_ID = Classroom_Info.Classroom_ID and Configuration_Info.Class_ID = Class_Info.Class_ID and major = '{0}' and Class = '{1}' and Grade = '{2}' and Course_Name = '{3}' and Teaching_Time like '{4}' order BY Teaching_Time ASC,Course_Name ASC", Major, Class, Grade, Class_CB.SelectedItem.ToString(), Time);
                    cmd.CommandText = sql1;
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                    DataSet data = new DataSet();
                    adapter.Fill(data);
                    DataTable table = data.Tables[0];
                    foreach (DataRow tr in table.Rows)
                    {
                        string pattern = @"\d\d";
                        Match match = Regex.Match(tr["Teaching_Time"].ToString(), pattern);
                        if (DL.Contains(tr["Classroom_Name"].ToString()) == false)
                        {
                            DL.Add(tr["Classroom_Name"].ToString());
                        }
                        if (TeacherL.Contains(tr["Teacher_Name"].ToString()) == false)
                        {
                            TeacherL.Add(tr["Teacher_Name"].ToString());
                        }
                        TimeL.Add(match.Value + "周");
                    }
                }
            }
            DiDian_CB.ItemsSource = null;
            Teacher_CB.ItemsSource = null;
            Time_CB.ItemsSource = null;
            DiDian_CB.ItemsSource = DL;
            Teacher_CB.ItemsSource = TeacherL;
            Time_CB.ItemsSource = TimeL;
            Time_CB.SelectedIndex = 0;
            DiDian_CB.SelectedIndex = 0;
            Teacher_CB.SelectedIndex = 0;
        }
    }
}
